package com.x.erp.dao;

import com.x.erp.model.ContractRegion;
import com.x.framework.dao.BaseDao;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;


@Repository
public class ContractRegionDao extends  BaseDao {


    private final static String selectContractRegionListSql = "SELECT T1.*, T2.REGION_NAME, T2.REGION_TYPE, T2.REGION_NUM , T2.REGION_REST_NUM, T3.CONTRACT_ID, T3.CONTRACT_START_DATE, T3.CONTRACT_ENDING_DATE, T4.SHOP_NAME,T4.SHOP_LEGAL_NAME,T4.SHOP_LEGAL_MOBILE,T5.MARKET_NAME FROM ERP_CONTRACT_REGION T1, ERP_REGION T2, ERP_CONTRACT T3, ERP_SHOP T4 , ERP_MARKET  T5 WHERE T1.REGION_ID = T2.REGION_ID AND T1.CONTRACT_ID = T3.CONTRACT_ID AND T3.SHOP_ID = T4.SHOP_ID AND T2.MARKET_ID = T5.MARKET_ID ";

    public List<ContractRegion> selectContractRegionList(ContractRegion contractRegion) throws Exception {
        StringBuilder sql = new StringBuilder(selectContractRegionListSql);
        List<Object> params = new ArrayList<Object>();
        if (this.isString(contractRegion.getContractId())) {
            sql.append("AND T1.CONTRACT_ID = ? ");
            params.add(contractRegion.getContractId());
        }
        if (this.isString(contractRegion.getContractRegionId())) {
            sql.append("AND T1.CONTRACT_REGION_ID = ? ");
            params.add(contractRegion.getContractRegionId());
        }
        if (this.isString(contractRegion.getRegionId())) {
            sql.append("AND T1.REGION_ID = ? ");
            params.add(contractRegion.getRegionId());
        }
        if (contractRegion.getContract() != null && this.isString(contractRegion.getContract().getShopId())) {
            sql.append("AND T3.SHOP_ID = ? ");
            params.add(contractRegion.getContract().getShopId());
        }
        sql.append("ORDER BY CONTRACT_REGION_ID DESC ");
        return this.queryForList(sql.toString(), ContractRegion.class, params, contractRegion);
    }

    /**
     * 查询表里面有效的合同明细使用面积
     */
    private final static String selectContractRegionSumSql = "SELECT SUM(T1.CONTRACT_REGION_RENT_COUNT)  FROM ERP_CONTRACT_REGION T1  WHERE  true  ";
    public Integer selectContractRegionSum(ContractRegion contractRegion) throws Exception {
        StringBuilder sql = new StringBuilder(selectContractRegionSumSql);
        List<Object> params = new ArrayList<Object>();
        if (this.isString(contractRegion.getRegionId())) {
            sql.append("AND T1.REGION_ID = ? AND T1.CONTRACT_REGION_STATE = ?");
            params.add(contractRegion.getRegionId());
            params.add(1);
        }
        return this.queryForInt(sql.toString(), params);
    }

}
